package com.example.calendertodo.util;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.util.Log;

import com.example.calendertodo.beam.Todo;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

public class TodoDbOpenHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "todoSQLite.db";
    private static final String TABLE_NAME_TODO = "calenderTodo";
    private static final String TABLE_NAME_USER = "calenderTodo";
    private static final String CREATE_TABLE_SQL = "create table " + TABLE_NAME_TODO + " (id integer primary key autoincrement, title text, content text, start_time text, end_time text)";
    private static final String CREATE_TABLE_SQL1 = "create table " + TABLE_NAME_TODO + " (id integer primary key autoincrement, title text, content text, start_time text, end_time text, state text, photo text)";
    private static final String CREATE_TABLE_SQL_USER = "create table " + TABLE_NAME_USER + " (id integer primary key autoincrement, username text, password text)";

    private final String STATE = "state";
    private final String PHOTO = "photo";
    private final int VERSION = 2;

    public TodoDbOpenHelper(Context context) {
        super(context, DB_NAME, null, 2);
    }

    //创建数据库的属性
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_SQL1);
        db.execSQL(CREATE_TABLE_SQL_USER);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {
        switch (i + 1) {
            case 2:
                String sql = "ALTER TABLE " + TABLE_NAME_TODO + " ADD COLUMN " + STATE + " VARCHAR(4) NOT NULL default 0";
                db.execSQL(sql);
                sql = "ALTER TABLE " + TABLE_NAME_TODO + " ADD COLUMN " + PHOTO + " VARCHAR(100)";
                db.execSQL(sql);
        }


    }


    //插入数据
    public long insertData(Todo todo) {
        SQLiteDatabase db = getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put("title", todo.getTitle());
        values.put("content", todo.getContent());
        values.put("start_time", todo.getStartTime());
        values.put("end_time", todo.getEndTime());
        values.put("state", todo.getState());
        values.put("photo", todo.getPhoto());

        return db.insert(TABLE_NAME_TODO, null, values);

    }

    //更新数据
    public int updateData(Todo todo) {

        SQLiteDatabase db = getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put("title", todo.getTitle());
        values.put("content", todo.getContent());
        values.put("start_time", todo.getStartTime());
        values.put("end_time", todo.getEndTime());
        values.put("state", todo.getState());
        values.put("photo", todo.getPhoto());

        return db.update(TABLE_NAME_TODO, values, "id like ?", new String[]{todo.getId()});
    }

    //删除数据
    public int deleteFromDbById(String id) {
        SQLiteDatabase db = getWritableDatabase();

        return db.delete(TABLE_NAME_TODO, "id like ?", new String[]{id});//like可以改为=、is
    }



    //查询所有数据(不包含取消的todo)
    public List<Todo> queryAllFromDb() {

        SQLiteDatabase db = getWritableDatabase();
        List<Todo> todoList = new ArrayList<>();

        Cursor cursor = db.query(TABLE_NAME_TODO, null, "state != ?", new String[]{"3"}, null, null, "start_time desc");
        if (cursor != null) {

            while (cursor.moveToNext()) {

                String id = cursor.getString(cursor.getColumnIndexOrThrow("id"));
                String title = cursor.getString(cursor.getColumnIndexOrThrow("title"));
                String content = cursor.getString(cursor.getColumnIndexOrThrow("content"));
                String start_time = cursor.getString(cursor.getColumnIndexOrThrow("start_time"));
                String end_time = cursor.getString(cursor.getColumnIndexOrThrow("end_time"));
                String state = cursor.getString(cursor.getColumnIndexOrThrow("state"));
                String photo = cursor.getString(cursor.getColumnIndexOrThrow("photo"));

                Todo todo = new Todo();
                todo.setId(id);
                todo.setTitle(title);
                todo.setContent(content);
                todo.setStartTime(start_time);
                todo.setEndTime(end_time);
                todo.setState(state);
                todo.setPhoto(photo);

                todoList.add(todo);
            }
            cursor.close();
        }

        return todoList;

    }

    //查询时间段内各种状态todo得事件数
    public int queryBetweenDate(String start,String end,String state) {
        String[] whereArgs={start, end,state};
        int count = 0;
        SQLiteDatabase db = getWritableDatabase();
        Cursor cursor = db.query(TABLE_NAME_TODO, null, "start_time between ? and ? and state = ?", whereArgs, null, null, null);
        if(cursor != null) {
            while (cursor.moveToNext()) {
                count++;
            }
            cursor.close();
        }
        Log.d("tag", "queryBetweenDate: "  + count);
        return count;
    }

    //查询时间段内todo事件数
    public int queryAllBetweenDate(String start,String end) {
        String[] whereArgs={start, end};
        int count = 0;
        SQLiteDatabase db = getWritableDatabase();
        Cursor cursor = db.query(TABLE_NAME_TODO, null, "start_time between ? and ?", whereArgs, null, null, null);
        if(cursor != null) {
            while (cursor.moveToNext()) {
                count++;
            }
            cursor.close();
        }
        Log.d("tag", "queryBetweenDate: "  + count);
        return count;
    }

    //查询某天是否有todo事件(不含取消)
    public int queryByDate(String date,String state) {
        int count = 0;
        SQLiteDatabase db = getWritableDatabase();
        Cursor cursor = db.query(TABLE_NAME_TODO, null, "start_time like ? and state != ?", new String[]{"%" + date + "%",state}, null, null, null);
        if(cursor != null) {
            while (cursor.moveToNext()) {
                count++;
            }
            cursor.close();
        }
        Log.d("tag", "queryBetweenDate: "  + count);
        return count;
    }


    //查询某天是否有未做的todo事件
    public int queryByDateAndState(String date,String state) {
        int count = 0;
        SQLiteDatabase db = getWritableDatabase();
        Cursor cursor = db.query(TABLE_NAME_TODO, null, "start_time like ? and state = ?", new String[]{"%" + date + "%",state}, null, null, null);
        if(cursor != null) {
            while (cursor.moveToNext()) {
                count++;
            }
            cursor.close();
        }
        Log.d("tag", "queryBetweenDate: "  + count);
        return count;
    }


    //根据标题进行查询
    public List<Todo> queryFromDbByTitle(String title) {
        if (TextUtils.isEmpty(title)) { //搜索框中无字符，显示所有
            return queryAllFromDb();
        }

        SQLiteDatabase db = getWritableDatabase();
        List<Todo> todoList = new ArrayList<>();

        //模糊查询，包含标题字符就显示
        Cursor cursor = db.query(TABLE_NAME_TODO, null, "title like ?", new String[]{"%" + title + "%"}, null, null, "start_time desc");

        if (cursor != null) {

            while (cursor.moveToNext()) {
                String id = cursor.getString(cursor.getColumnIndexOrThrow("id"));
                String title2 = cursor.getString(cursor.getColumnIndexOrThrow("title"));
                String content = cursor.getString(cursor.getColumnIndexOrThrow("content"));
                String start_time = cursor.getString(cursor.getColumnIndexOrThrow("start_time"));
                String end_time = cursor.getString(cursor.getColumnIndexOrThrow("end_time"));
                String state = cursor.getString(cursor.getColumnIndexOrThrow("state"));
                String photo = cursor.getString(cursor.getColumnIndexOrThrow("photo"));

                Todo todo = new Todo();
                todo.setId(id);
                todo.setTitle(title2);
                todo.setContent(content);
                todo.setStartTime(start_time);
                todo.setEndTime(end_time);
                todo.setState(state);
                todo.setPhoto(photo);

                todoList.add(todo);
            }
            cursor.close();
        }
        return todoList;
    }


    //根据日历上选中的日期显示Todo事件(不包含取消的)
    public List<Todo> queryFromDbByDate(String date) {

        SQLiteDatabase db = getWritableDatabase();
        List<Todo> todoList = new ArrayList<>();

        Cursor cursor = db.query(TABLE_NAME_TODO, null, "start_time like ? and state != ?", new String[]{"%" + date + "%","3"}, null, null, "start_time");

        if (cursor != null) {

            while (cursor.moveToNext()) {
                String id = cursor.getString(cursor.getColumnIndexOrThrow("id"));
                String title2 = cursor.getString(cursor.getColumnIndexOrThrow("title"));
                String content = cursor.getString(cursor.getColumnIndexOrThrow("content"));
                String start_time = cursor.getString(cursor.getColumnIndexOrThrow("start_time"));
                String end_time = cursor.getString(cursor.getColumnIndexOrThrow("end_time"));
                String state = cursor.getString(cursor.getColumnIndexOrThrow("state"));
                String photo = cursor.getString(cursor.getColumnIndexOrThrow("photo"));

                Todo todo = new Todo();
                todo.setId(id);
                todo.setTitle(title2);
                todo.setContent(content);
                todo.setStartTime(start_time);
                todo.setEndTime(end_time);
                todo.setState(state);
                todo.setPhoto(photo);
                todoList.add(todo);
            }
            cursor.close();
        }
        return todoList;
    }
}
